package com.ht.util;

import com.ht.config.DBConfig;

import java.sql.*;

public class DBHelper {

    private static Connection conn = null;

    static {
        try {
            Class.forName(DBConfig.DRIVER);
            conn = DriverManager.getConnection(DBConfig.URl, DBConfig.USERNAME, DBConfig.PASSOWORD);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库连接
     * @return Connection
     */
    public static Connection getConn() {
        if (conn == null) {
            try {
                Class.forName(DBConfig.DRIVER);
                conn = DriverManager.getConnection(DBConfig.URl, DBConfig.USERNAME, DBConfig.PASSOWORD);
                return conn;
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return conn;
    }


    /**
     * 根据SQL语句查询
     *
     * @param sql 要执行的SQL语句
     * @return 返回查询的结果集
     */
    public static ResultSet querySQL(String sql) {
        try {
            Statement stmt = conn.createStatement();
            return stmt.executeQuery(sql);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 执行更新、删除、修改的SQL语句
     *
     * @param sql 要执行的SQL语句
     * @return 返回执行结果
     */
    public static boolean executeSQL(String sql) {
        try {
            Statement stmt = conn.createStatement();
            int i = stmt.executeUpdate(sql);
            return i > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 执行预编译SQL
     *
     * @param sql    要执行的SQL语句
     * @param params 要设置的参数
     * @return 返回结果集
     */
    public static ResultSet querySQL(String sql, String... params) {
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                pstmt.setString(i + 1, params[i]);
            }
            return pstmt.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 执行更新
     *
     * @param sql    要执行更新的SQL
     * @param params 参数
     * @return 更新与否
     */
    public static boolean executeUpdateSQL(String sql, String... params) throws SQLException {
        PreparedStatement pstmt = conn.prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            pstmt.setString(i + 1, params[i]);
        }
        int i = pstmt.executeUpdate();
        return i > 0;
    }

    /**
     * 执行插入
     *
     * @param sql    SQL
     * @param params 参数
     * @return 插入与否
     */
    public static int executeInsertSQL(String sql, String... params) throws SQLException {

        PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        for (int i = 0; i < params.length; i++) {
            pstmt.setString(i + 1, params[i]);
        }
        pstmt.executeUpdate();
        ResultSet keys = pstmt.getGeneratedKeys();
        if (keys.next()) {
            return keys.getInt(1);
        } else {
            return -1;
        }

    }

    /**
     * 执行删除SQL
     *
     * @param sql    SQL
     * @param params 参数
     * @return 成功与否
     */
    public static boolean executeDeleteSQL(String sql, String... params) throws SQLException {
        return DBHelper.executeUpdateSQL(sql, params);
    }

}
